<?php
/**
 * Desc: Service模板
 * User: zdb
 * Date: 2023/6/5
 * Time: 11:22
 */

namespace app\api\service\resource;
use app\api\service\setting\DictionarySubService;
use app\util\DateUtil;
use think\facade\Db;

class LibWordEnService
{

    private $primaryTable;
    private $typeTable;
    private $meaningTable;
    private $sentenceTable;
    private $phraseTable;

    public function __construct()
    {
        $this->primaryTable = "yj_lib_word_en";
        $this->typeTable = "yj_lib_word_en_type";
        $this->meaningTable = "yj_lib_word_en_meaning";
        $this->sentenceTable = "yj_lib_word_en_sentence";
        $this->phraseTable = "yj_lib_word_en_phrase";
    }

    public function findRowById($id)
    {
        return Db::table($this->primaryTable)->alias('a')
            ->where('id', '=', $id)
            ->strict(false)
            ->find();
    }

    public function findRowByData($data)
    {
        $condition = $this->assembleFilterCondition($data);
        return Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->strict(false)
            ->find();
    }

    public function getListByManyId($arrId)
    {
        $list = Db::table($this->primaryTable)->alias('a')
            ->whereIn('id', $arrId)
            ->select()
            ->toArray();
        return $list;
    }

    public function assembleFilterCondition($data)
    {
        $condition = ['a.status > 0'];

        if (!empty($data['spelling'])) {
            $condition[] = "a.spelling like '%" . $data['spelling'] . "%'";
        }

        if (!empty($data['arr_id'])) {
            $condition[] = "a.id in (" . implode(",", $data['arr_id']). ")";
        }

        return empty($condition) ? "" : implode(" AND ", $condition);
    }

    public function getListNoPage($data = [], $limitNum = 30)
    {
        $condition = $this->assembleFilterCondition($data);
        $res = Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->limit(0, $limitNum)
            ->order('a.id desc')
            ->select()
            ->toArray();
        return $res;
    }

    public function getListWithPage($data, $pageIndex = 1, $pageSize = 30)
    {
        $offset = ($pageIndex - 1) * $pageSize;
        $condition = $this->assembleFilterCondition($data);
        $res = Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->limit($offset, $pageSize)
            ->order('a.id desc')
            ->select();
        return $res;
    }

    public function getTotalNum($data)
    {
        $condition = $this->assembleFilterCondition($data);
        $num = Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->count();
        return $num;
    }

    public function createRow($data)
    {
        $data['status'] = isset($data['status']) ? $data['status']:1;
        $res = Db::table($this->primaryTable)
            ->strict(false)
            ->duplicate($data)
            ->insert($data, true);
        return $res;
    }

    public function createManyRow($data)
    {
        $res = Db::table($this->primaryTable)
            ->strict(false)
            ->duplicate($data)
            ->insertAll($data, true);
        return $res;
    }

    public function updateRowById($id, $data)
    {
        $res = Db::table($this->primaryTable)->alias('a')
            ->where('id', '=', $id)
            ->strict(false)
            ->save($data);
        return false !== $res ? true : false;
    }

    public function updateListByData($condition, $data)
    {
        $condition = $this->assembleFilterCondition($data);
        $res = Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->save($data);
        return false !== $res ? true : false;
    }

    public function updateListByManyId($arrId, $data)
    {
        $res = Db::table($this->primaryTable)->alias('a')
            ->whereIn('a.id', $arrId)
            ->strict(false)
            ->save($data);
        return false !== $res ? true : false;
    }

    public function removeRowById($id)
    {
        $data = ['status' => 0];
        $res = Db::table($this->primaryTable)
            ->where('id', '=', $id)
            ->strict(false)
            ->save($data);
        return false !== $res ? true : false;
    }

    public function removeListByManyId($arrId)
    {
        $data = ['status' => 0];
        $res = Db::table($this->primaryTable)->alias('a')
            ->whereIn('a.id', $arrId)
            ->strict(false)
            ->save($data);
        return false !== $res ? true : false;
    }

    public function removeListByData($data)
    {
        $condition = $this->assembleFilterCondition($data);
        $data = ['status' => 0];
        $res = Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->save($data);
        return false !== $res ? true : false;
    }

    /**
     * 先几种词性，再每种词性的意思，最后每个意思下的例句
     * 相关词组及释义
     * @param $wordId
     */
    public function findWordDetailById($wordId)
    {
        $wordMeaningList = Db::table($this->meaningTable)->alias("m")
            ->leftJoin($this->typeTable." t", "t.id = m.word_type_id")
            ->leftJOin($this->primaryTable." w", "w.id = t.word_id")
            ->where('w.id', '=', $wordId)
            ->field("m.id as word_meaning_id, m.word_type_id, m.meaning_en, m.meaning_cn, t.part_of_speech, t.speech_text")
            ->order('t.part_of_speech', 'asc')
            ->select()
            ->toArray();
        $arrMeaningId = array_column($wordMeaningList, 'word_meaning_id');

        $sentenceList = Db::table($this->sentenceTable)->alias("s")
            ->whereIn('s.subject_id', $arrMeaningId)
            ->where('s.cate_type', '=', 1)
            ->select()
            ->toArray();

        //根据意思将例句分组
        $sentenceListByMeaningId = array();
        foreach($sentenceList as $sentenceItem){
            $wordMeaningId = $sentenceItem['subject_id'];
            if(!isset($sentenceListByMeaningId[$wordMeaningId])){
                $sentenceListByMeaningId[$wordMeaningId] = array();
            }
            $sentenceListByMeaningId[$wordMeaningId][] = $sentenceItem;
        }

        //获取单词相关词组
        $phraseList = $this->getWordPhraseList($wordId);
        $arrPhraseId = array_column($phraseList, 'id');
        $phraseInfoById = array_column($phraseList, null, 'id');
        $phraseSentenceList = Db::table($this->sentenceTable)->alias("s")
            ->whereIn('s.subject_id', $arrPhraseId)
            ->where('s.cate_type', '=', 2)
            ->select()
            ->toArray();
        //根据词组将例句分组
        $sentenceListByPhraseId = array();
        foreach($phraseSentenceList as $sentenceItem){
            $wordPhraseId = $sentenceItem['subject_id'];
            if(!isset($sentenceListByPhraseId[$wordPhraseId])){
                $sentenceListByPhraseId[$wordPhraseId] = array();
            }
            $sentenceListByPhraseId[$wordPhraseId][] = $sentenceItem;
        }

        foreach($phraseList as &$phraseItem){
            if(isset($sentenceListByPhraseId[$phraseItem['id']])){
                $phraseItem['sentence_list'] = $sentenceListByPhraseId[$phraseItem['id']];
            }else{
                $phraseItem['sentence_list'] = array();
            }
        }

        $wordTypeItem = array();
        foreach($wordMeaningList as $meaningItem){
            if(!isset($wordTypeItem[$meaningItem['word_type_id']])){
                $wordTypeItem[$meaningItem['word_type_id']] = array(
                    'word_type_id' => $meaningItem['word_type_id'],
                    'part_of_speech' => $meaningItem['part_of_speech'],
                    'speech_text' => $meaningItem['speech_text'],
                );
            }
            $meaningItem['sentence_list'] = $sentenceListByMeaningId[$meaningItem['word_meaning_id']] ?? [];
            $wordTypeItem[$meaningItem['word_type_id']]['meaning_list'][] = $meaningItem;
        }
        $detailData = array(
            'word_explanation' => array_values($wordTypeItem),
            'word_phrase' => $phraseList,
        );
        return $detailData;
    }

    public function getWordPhraseList($wordId)
    {
        $phraseList = Db::table($this->phraseTable)->alias("p")
            ->where('word_id', '=', $wordId)
            ->select()
            ->toArray();
        return $phraseList;
    }

    /**
     * 添加新单词
     * @param $data
     */
    public function addNewWord($data)
    {
        $spelling = $data['spelling'];
        $serviceDictionarySub = new DictionarySubService();
        $wordTypeList = $serviceDictionarySub->getListNoPage(array('dictionary_code' => 'vocabulary_type'));
        $wordTypeShortNameByNum = array_column($wordTypeList, 'sub_name_en', 'sub_value');
        $nowTime = DateUtil::getCurrentDatetime();
        try{
            Db::table($this->phraseTable)->startTrans();
            $newWordId = $this->createRow(array('spelling' => $spelling, 'update_time' => $nowTime));
            $serviceWordTypeEn = new LibWordEnTypeService();
            $newWordTypeId = 0;
            foreach($data['word_type'] as $wordTypeValue){
                $wordTypeRowData = array(
                    'word_id'        => $newWordId,
                    'part_of_speech' => $wordTypeValue,
                    'speech_text' => $wordTypeShortNameByNum[$wordTypeValue],
                    'update_time' => $nowTime
                );
                $newWordTypeId = $serviceWordTypeEn->createRow($wordTypeRowData);
            }
            Db::table($this->phraseTable)->commit();
            return [$newWordId, $newWordTypeId];
        }catch(\Exception $e){
            Db::table($this->phraseTable)->rollback();
            throw new \Exception($e);
        }
    }
}